【Googleスプレッドシート×Slack】関数だけでコストの使いすぎアラートをできる限り自動化してみた

■最初に

社内メンバーが利用しているサービスの月額利用費の締め処理をしています。
締め処理に加え、先月から、以下の取り組みを実施しています。

・月額5000円以上の利用料を出したユーザーと管理職にSlackで通知

利用ID毎の利用費はBigQueryでGoogleスプレッドシートに抽出されます。

通常であれば、ZapierやIFTTTでSlackを連携して通知するのがスマートなのですが
「誰でもメンテナンスが可能」
「でも人力箇所はなるべく少な目に」
をベースに非エンジニアでもメンテナンス可能な方法を考えました。

■準備

利用費・利用者を抽出するための準備です。

①BigQueryの抽出データとは別シートに、ID毎の利用費をピボットテーブルで抽出します
-A列：ID
-B列：利用費
・シート名「ピボット テーブル 2」
<イメージ>

②ID情報のシートを準備します。ID、部門、利用者のデータが必要になります
-A列：利用者名
-B列：ID
-C列：部門名
・シート名「ID一覧」
<イメージ>

③部門シートを準備します。部門、部門管理者のデータが記載されています
-A列：部門名
-B列：部門管理者
・シート名「部門」
<イメージ>

④Slackで通知する文言を考えます。
＜例＞

---

@利用者 @部門管理者
前月の利用料金が 5000円 を超えましたので、お知らせいたします:moneybag:

詳細:
部門名:XXXX
プロジェクト名:利用者
利用料金: XXXX円

お手数ですが、管理者におきましては、利用状況をご確認の上、本スレッドに問題ない旨のご返信をお願いいたします:pray:
ご不明な点がございましたら、お気軽にご連絡ください:speech_balloon:

---

以上で準備は整いました。

Slack報告用文言の作成

①更にGoogleスプレッドシートで新シートを用意し、A～Fに以下のようなタイトルを入力します。
-A列：ID
-B列：月額利用費
-C列：部門
-D列：部門管理者
-E列：利用者
-F列：Slack文言
（※Haradaは68行目に上記データを入力しています）

②A列：IDの下に以下関数を入力します。
FILTER関数は、Googleスプレッドシートで指定した条件に合致するデータのみを抽出する関数でDBから特定の条件でデータを絞り込むイメージで使用することができます

=FILTER('ピボット テーブル 2'!A:B, 'ピボット テーブル 2'!B:B >= 5000)

※他のブックから抽出する場合はIMPORTRANGE関数を組み込むことで実現可能です。

A列：IDの直下に5000円以上の利用費を使ったID、
B列：月額利用費の直下に5000円以上の利用費金額が表示されました。

③C列：部門の下に以下関数を入力します。
簡単なVLOOKUP関数を利用します。

=VLOOKUP(A69,'ID一覧'!$B$2:$C$16,2,FALSE)

対象IDが利用されている部門が抽出されます。

④D列：部門管理者の下に以下関数を入力します
③で抽出した部門をキーにVLOOKUP関数で部門管理者を抽出します。

=VLOOKUP(C69,'部門'!$A$2:$B$5,2,FALSE)

⑤E列：利用者の下に以下関数を入力します

=INDEX('ID一覧'!A:A,MATCH(A69,'ID一覧'!B:B,0))

「ID一覧」シートで、利用者はIDの左側の列にあるためVLOOKUPが使えませんでした。
代わりに、INDEX関数を使います。
INDEX関数は、行番号と列番号を指定して、値を抽出する関数です。

⑥F列：Slack文言の下に以下関数を入力します
カナリ強引です…。
・CHAR(10) を使いスプレッドシートの関数内で改行させます。
・利用しているサービス利用費が、時折 小数点も出してくるためINT・ROUNDUP関数を利用し少数第一位の値を四捨五入し、整数に変換してます
・既に手順②で5000円以上のIDを抽出していますが、念のため「>5000」で再確認しています。

=IF(B69>5000,CONCATENATE("@",E69," @",D69, CHAR(10),"前月の利用料金が 5000円 を超えましたので、お知らせいたします:お金の袋:",CHAR(10),CHAR(10),"詳細:",CHAR(10),"部門名: ",69,CHAR(10),"プロジェクト名: ",A69,CHAR(10),"利用料金: ",INT(ROUNDUP(B69,0))," 円",CHAR(10),CHAR(10),"お手数ですが、管理者におきましては、利用状況をご確認の上、本スレッドに問題ない旨のご返信をお願いいたします:祈る:",CHAR(10),"ご不明な点がございましたら、お気軽にご連絡ください:入力中アイコン:"),"")

できあがったのがこちら

⑦Slackに投稿してみる
F列に生成された「Slack文言」をSlackにコピー＆ペーストします。

良い感じにできました。
次月からはF列の生成された文言をコピー＆ペーストするだけで良さそうです。

注意点としては、メンション先がメンション名を変えている場合は修正したり、
見やすさを工夫するなら体裁を整えたりと微調整が必要になります

後述

原始的な方法を紹介しましたが、
利用者・部門管理者・ID・利用費をそれぞれの対象シートからコピー＆ペーストし
ダブルチェック等も実施していたため、手作業が大幅に減りました。

また、関数の組み合わせのため、APIやプログラミングの知識なしで
誰でも容易にメンテナンスができます。

何かの参考になれば…。